package com.augurit.gzcgwsp.qyzz.service.impl;

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dozer.DozerBeanMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;

import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.qyzz.dao.YwRqQyzzAzWxRyZsDao;
import com.augurit.gzcgwsp.qyzz.entity.YwRqQyzzAzWxRyZs;
import com.augurit.gzcgwsp.qyzz.entity.YwRqQyzzFinalAzWxRyZs;
import com.augurit.gzcgwsp.qyzz.entity.YwRqQyzzFinalJsRy;
import com.augurit.gzcgwsp.qyzz.entity.YwRqQyzzJsRy;
import com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzAzWxRyZsService;
import com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzFinalAzWxRyZsService;

/**
 * 
 *  企业资质--燃气燃烧器具安装维修人员持证情况service实现类
 * @author 张军
 *
 */
@Service
@Transactional
public class YwRqQyzzAzWxRyZsServiceImpl  implements IYwRqQyzzAzWxRyZsService {
	private final static Log log=LogFactory.getLog(YwRqQyzzAzWxRyZsServiceImpl.class);
	 @Autowired
	private YwRqQyzzAzWxRyZsDao ywRqQyzzAzWxRyZsDao;
     @Autowired
    private IYwRqQyzzFinalAzWxRyZsService ywRqQyzzFinalAzWxRyZsService;

	public void saveEntity(YwRqQyzzAzWxRyZs entity) {
		ywRqQyzzAzWxRyZsDao.save(entity);
		
	}

	public List<YwRqQyzzAzWxRyZs> getAZWxRyZs(long qyid, String acceptno) {
		return ywRqQyzzAzWxRyZsDao.getAzWxRyZs(qyid, acceptno);
	}


	/*
	 * 燃气燃烧器具安装维修人员持证情况
	 * @see com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzJsRyService#importQyzzInfo(jxl.Sheet, java.lang.String, long, java.lang.String)
	 */
public String importAZWxRyZs(Sheet sheet, String acceptNo, long qyid,
		String username,Map map) {
	String errors="";
	String process=(String) map.get("process");
	if("cksa".equals(process)){
//    	List<YwRqQyzzFinalAzWxRyZs> list=ywRqQyzzFinalAzWxRyZsService.getAZWxRyZs(qyid,acceptNo);
//    	if(list!=null && list.size()>0){
//    		for(YwRqQyzzFinalAzWxRyZs entity:list){
//    			//先删除
//    		    ywRqQyzzFinalAzWxRyZsService.delete(entity);
//    		}
//    	}
    	//新增
    	int rows=sheet.getRows();
    	for(int i=2;i<rows;i++){
    		String name=ExcelUtil.readData(sheet, 1, i);//姓名
    		String idCard=ExcelUtil.readData(sheet, 2, i);//身份证号码
    		String zyjnzsbh=ExcelUtil.readData(sheet,3, i);//职业技能岗位证书编号
    		String fzbm=ExcelUtil.readData(sheet, 4, i);//发证部门
    		if(isHasData(name,zyjnzsbh,idCard,fzbm)){
    		    YwRqQyzzFinalAzWxRyZs entity=new YwRqQyzzFinalAzWxRyZs();
    			entity.setQyid(qyid);
    			entity.setIdCard(idCard);
    			entity.setZyjnzsbh(zyjnzsbh);
    			entity.setFzDept(fzbm);
    			entity.setName(name);
    			try {
    			    ywRqQyzzFinalAzWxRyZsService.saveEntity(entity);
    			} catch (Exception e) {
    				log.error("第三个工作表"+"第"+i+"行出现错误"+e);
    				errors+="第三个工作表"+"第"+i+"行出现错误\n";				
    			}
    		}
    	}
	}else{
	    List<YwRqQyzzAzWxRyZs> list=ywRqQyzzAzWxRyZsDao.getAzWxRyZs(qyid,acceptNo);
	    if(list!=null && list.size()>0){
	        for(YwRqQyzzAzWxRyZs entity:list){
	            //先删除
	            ywRqQyzzAzWxRyZsDao.delete(entity);
	        }
	    }
	    //新增
	    int rows=sheet.getRows();
	    for(int i=2;i<rows;i++){
	        String name=ExcelUtil.readData(sheet, 1, i);//姓名
	        String idCard=ExcelUtil.readData(sheet, 2, i);//身份证号码
	        String zyjnzsbh=ExcelUtil.readData(sheet,3, i);//职业技能岗位证书编号
	        String fzbm=ExcelUtil.readData(sheet, 4, i);//发证部门
	        if(isHasData(name,zyjnzsbh,idCard,fzbm)){
	            YwRqQyzzAzWxRyZs entity=new YwRqQyzzAzWxRyZs();
	            entity.setAcceptNo(acceptNo);//受理编号
	            entity.setQyid(qyid);
	            entity.setIdCard(idCard);
	            entity.setZyjnzsbh(zyjnzsbh);
	            entity.setFzDept(fzbm);
	            entity.setName(name);
	            try {
	                ywRqQyzzAzWxRyZsDao.save(entity);
	            } catch (Exception e) {
	                log.error("第三个工作表"+"第"+i+"行出现错误"+e);
	                errors+="第三个工作表"+"第"+i+"行出现错误\n";               
	            }
	        }
	    } 
	}
	return errors;
}
	
	/**
	 * 判断是否有数据
	 * @param name
	 * @param idCard
	 * @param zsbh
	 * @param fzbm
	 * @return
	 */
	public boolean isHasData(String name,String idCard,String zsbh,String fzbm){
		if(StringUtils.isNotBlank(name)&&StringUtils.isNotBlank(idCard)
			&&StringUtils.isNotBlank(zsbh) && StringUtils.isNotBlank(fzbm) )
			return true;
		else 
			return false;
		
	}
	
	
	  public static void main(String[] args) throws BiffException, IOException{
		  String filepath="c:\\安装维修企业资质申请表922.xls";
		   File file=new File(filepath);
			Workbook wb = Workbook.getWorkbook(file);
			Sheet sheet=wb.getSheet(2);
			int rowss=sheet.getRows();
			System.out.print(rowss);
			String dwmc=ExcelUtil.readData(sheet,1, 2);//单位名称
			System.out.print(dwmc);
	   }


	  /*
	   * excel填充数据
	   * @see com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzAzWxRyZsService#copyWritableSheet(jxl.write.WritableSheet, java.lang.String, long)
	   */
	public void copyWritableSheet(WritableSheet wsThree, String acceptNo,
			long qyid,String type) {
	    if("qyxx".equals(type)){
	        List<YwRqQyzzFinalAzWxRyZs> list=ywRqQyzzFinalAzWxRyZsService.getAZWxRyZs(qyid, acceptNo);
	        for(int i=0;i<list.size();i++){
	            try {
	                YwRqQyzzFinalAzWxRyZs entity=list.get(i);
	                String idcard=entity.getIdCard()==null?"":entity.getIdCard();//身份证
	                String name=entity.getName()==null?"":entity.getName();//姓名
	                String fzbm=entity.getZyjnzsbh()==null?"":entity.getZyjnzsbh();//职业技能岗位证书编号
	                String fzdept=entity.getFzDept()==null?"":entity.getFzDept();//发证部门
	                WritableCellFormat wcf = new WritableCellFormat();
	                wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
	                wcf.setAlignment(Alignment.CENTRE);
	                wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
	                ExcelUtil.modiStrCell2(wsThree, 0,  i+2, i+1+"", wcf);
	                ExcelUtil.modiStrCell2(wsThree, 1,  i+2, name, wcf);
	                ExcelUtil.modiStrCell2(wsThree, 2,  i+2, idcard, wcf);
	                ExcelUtil.modiStrCell2(wsThree, 3,  i+2, fzbm, wcf);
	                ExcelUtil.modiStrCell2(wsThree, 4,  i+2, fzdept, wcf);

	            } catch (Exception e) {
	                log.error("受理编号"+acceptNo+"导出企业专业技术人员状况错误"+e);
	            } 
	        
	    }
	    }else{
	        List<YwRqQyzzAzWxRyZs> list=ywRqQyzzAzWxRyZsDao.getAzWxRyZs(qyid,acceptNo);
	        for(int i=0;i<list.size();i++){
	            try {
	                YwRqQyzzAzWxRyZs entity=list.get(i);
	                String idcard=entity.getIdCard()==null?"":entity.getIdCard();//身份证
	                String name=entity.getName()==null?"":entity.getName();//姓名
	                String fzbm=entity.getZyjnzsbh()==null?"":entity.getZyjnzsbh();//职业技能岗位证书编号
	                String fzdept=entity.getFzDept()==null?"":entity.getFzDept();//发证部门
	                WritableCellFormat wcf = new WritableCellFormat();
	                wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
	                wcf.setAlignment(Alignment.CENTRE);
	                wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
	                ExcelUtil.modiStrCell2(wsThree, 0,  i+2, i+1+"", wcf);
	                ExcelUtil.modiStrCell2(wsThree, 1,  i+2, name, wcf);
	                ExcelUtil.modiStrCell2(wsThree, 2,  i+2, idcard, wcf);
	                ExcelUtil.modiStrCell2(wsThree, 3,  i+2, fzbm, wcf);
	                ExcelUtil.modiStrCell2(wsThree, 4,  i+2, fzdept, wcf);

	            } catch (Exception e) {
	                log.error("受理编号"+acceptNo+"导出企业专业技术人员状况错误"+e);
	            } 
	        
	    }
	    }

 }

    public Map importFinalData(Long qyid, String acceptNo) {
        List<YwRqQyzzAzWxRyZs> list=ywRqQyzzAzWxRyZsDao.getAzWxRyZs(qyid,acceptNo);
        for(YwRqQyzzAzWxRyZs entity:list){
            YwRqQyzzFinalAzWxRyZs finalEntity = new DozerBeanMapper().map(entity, YwRqQyzzFinalAzWxRyZs.class);
            finalEntity.setId(null);
            this.ywRqQyzzFinalAzWxRyZsService.saveEntity(finalEntity);
        }
        return null;
    }

   
    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzAzWxRyZsService#getPersonData(java.lang.String, java.lang.String, java.lang.Long)
     */
	public List<Long> getPersonData(String name, String idCard, Long qyid) {
		String sql="select qyid from " +YwRqQyzzAzWxRyZs.class.getName()+
		" where name='"+name+"'" +" and idCard='"+idCard+"'"+" and qyid!="+qyid;
		return this.ywRqQyzzAzWxRyZsDao.find(sql);
	}

	public Page<YwRqQyzzAzWxRyZs> search(Page<YwRqQyzzAzWxRyZs> page, Map map) {
		String acceptNo=(String) map.get("acceptNo");
		String hql=" from "+YwRqQyzzAzWxRyZs.class.getName()+" where acceptNo='"+acceptNo+"'";
		Map<String,String> values=new HashMap<String,String>();
		values.put("acceptNo", acceptNo);
		page=ywRqQyzzAzWxRyZsDao.findPage(page, hql, values);
		return page;
	}



	
	 
}
